﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DAL_Users
/// </summary>
public class DAL_Users : clsConnection
{
    DataTable dt = new DataTable();
    clsQueryList clsquery = new clsQueryList();
    public DataTable DAL_Users_getAllData()
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM Users");
        }
        catch
        {
        }
        return dt;
    }
    public DataTable DAL_Users_Login(string where)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM Users WHERE " + where + "");
        }
        catch
        {
        }
        return dt;
    }
    public DataTable DAL_Users_getAllById(int Id)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM Users WHERE Uid = " + Id);
        }
        catch
        {
        }
        return dt;
    }
    public DataTable DAL_Users_getAllByEmail(string email)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM Users WHERE Email = '" + email + "'");
        }
        catch
        {
        }
        return dt;
    }
    public DataTable DAL_Users_getAllByPer(int per)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM Users WHERE Permissions_Id = " + per);
        }
        catch
        {
        }
        return dt;
    }
    public Boolean DAL_Users_addtodel(int id)
    {
        try
        {
            string sql = "delete from Users where Uid=" + id;
            clsquery.addSQL(sql);
        }
        catch
        {
            return false;
        }
        return true;
    }
    public Boolean DAL_Users_del()
    {
        try
        {
            clsquery.Execute();
        }
        catch
        {
            return false;
        }
        return true;
    }
    public Boolean DAL_Users_Update(DTO_Users obj)
    {
        try
        {
            string sql = "UPDATE Users SET Name = @name, Email = @email, Status = @status  WHERE Uid = @uid";
            AddParameter(new SqlParameter("@name", obj.Name));
            AddParameter(new SqlParameter("@email", obj.Email));
            AddParameter(new SqlParameter("@status", obj.Status));
            AddParameter(new SqlParameter("@uid", obj.Uid));

            if (ExcuteNonQuery(sql))
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        catch
        {
            return false;
        }
    }
    public Boolean DAL_Users_UpdatePassword(DTO_Users obj)
    {
        try
        {
            string sql = "UPDATE Users SET Password = @password WHERE Uid = @uid";
            AddParameter(new SqlParameter("@password", obj.Password));
            AddParameter(new SqlParameter("@uid", obj.Uid));

            if (ExcuteNonQuery(sql))
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        catch
        {
            return false;
        }
    }
    public Boolean DAL_Users_Insert(DTO_Users obj)
    {
        try
        {
            string sql = "INSERT INTO Users (UserName, Name, Email, Permissions_Id, Status, Password) VALUES (@username, @name,@email, @permissions_id, @status, @password)";
            AddParameter(new SqlParameter("@username", obj.UserName));
            AddParameter(new SqlParameter("@name", obj.Name));
            AddParameter(new SqlParameter("@email", obj.Email));
            AddParameter(new SqlParameter("@permissions_id", obj.Permissions_Id));
            AddParameter(new SqlParameter("@status", obj.Status));
            AddParameter(new SqlParameter("@password", obj.Password));

            if (ExcuteNonQuery(sql))
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        catch
        {
            return false;
        }
    }
    public DataTable DAL_Users_getByStatus(int status)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM Users WHERE Status = " + status);
        }
        catch
        {
        }
        return dt;
    }
}